package org.dashboard.service.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import netscape.javascript.JSObject;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.ObjectUtils;
import org.dashboard.bean.*;
import org.dashboard.constant.Constant;
import org.dashboard.constant.DbSourceTypeEnum;
import org.dashboard.dao.*;
import org.dashboard.service.*;
import org.dashboard.utils.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;

import java.io.IOException;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

@Service(interfaceClass = AdIndexService.class)
@CacheConfig(cacheManager = Constant.DEFAULT_CACHE_MANAGER)
public class AdIndexServiceImpl implements AdIndexService {
    private static final Logger logger = LoggerFactory.getLogger(AdIndexServiceImpl.class);

    @Autowired
    private AdPageService adPageService;

    @Autowired
    private AdChartService adChartService;

    @Autowired
    private AdDatasetService adDatasetService;

    @Autowired
    private AdSchemaService adSchemaService;

    @Autowired
    private AdFolderService adFolderService;

    @Autowired
    private AdDatasourceService adDatasourceService;

    @Autowired
    TemplateService templateService;

    @Override
    public List<Map<String, Object>> loadChartData(AdDataset adDataset, List<Map<String, Object>> params, AdUser user, List<AdRole> roles) {
        List<Map<String, Object>> data = new ArrayList<>();
        try {
            JSONObject dataSetDefine = (JSONObject) JSON.parse(adDataset.getDatasetData());
            if (dataSetDefine.getString("DataServiceType").equals("database")) {
                data = loadFromDatabase(data, dataSetDefine, params, user, roles);
            } else if (dataSetDefine.getString("DataServiceType").equals("service")) {
                data = loadFromService(data, dataSetDefine, params, user, roles);
            } else {
                //TODO: Support Other
            }
        } catch (Exception e) {
            logger.error("loadChartData", e);
        }

        return data;
    }

    private List<Map<String, Object>> loadFromDatabase(List<Map<String, Object>> data, JSONObject dataSetDefine, List<Map<String, Object>> params, AdUser user, List<AdRole> roles) {
        try {
            Integer dataSourceId = dataSetDefine.getJSONObject("DataServiceMeta").getInteger("DataSouceLink");

            AdDatasource adDatasource = adDatasourceService.selectByPrimaryKey(dataSourceId);
            List<Object> dataColumnsMap = dataSetDefine.getJSONArray("DataColumnsMap");
            String querySqlString = dataSetDefine.getJSONObject("DataServiceMeta").getString("QuerySqlString");
            List<Object> querySqlParams = dataSetDefine.getJSONObject("DataServiceMeta").getJSONArray("QuerySqlParams");

            Map<String, Object> templateModel = new Hashtable<>();

            for (Object querySqlParam : querySqlParams) {
                JSONObject queryParam = (JSONObject) querySqlParam;
                String paramName = queryParam.getString("ParamName");
                String paramValue = queryParam.getString("DefaultValue");

                for (Map<String, Object> param : params) {
                    if (param.get("ParamName").toString().equals(paramName)) {
                        paramValue = param.get("ParamValue").toString();
                    }
                }

                templateModel.put(paramName, paramValue);
            }

            if (user != null && querySqlString.contains(Constant.DATA_PERMISSION_USER_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_USER_IDENTITY, user.getUserId().toString());
            }

            if (roles != null && roles.size() > 0 && querySqlString.contains(Constant.DATA_PERMISSION_ROLE_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_ROLE_IDENTITY, roles.get(0).getRoleId().toString());
            }

            if (roles != null && roles.size() > 0 && querySqlString.contains(Constant.DATA_PERMISSION_ROLES_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_ROLES_IDENTITY, String.format("(%s)", roles.stream().map(AdRole::getRoleId).map(Object::toString).collect(Collectors.joining(","))));
            }

            if (!templateModel.isEmpty()) {
                try {
                    Template template = templateService.createTemplate("QuerySqlString_" + EncryptUtil.md5(querySqlString), querySqlString);
                    querySqlString = FreeMarkerTemplateUtils.processTemplateIntoString(template, templateModel);
                } catch (IOException | TemplateException e) {
                    logger.error("FreeMarkerTemplateUtils", e);
                }
            }

            DbSourceTypeEnum dbSourceTypeEnum = DbTypeTools.getDataSourceEnum(adDatasource.getDatasourceType());
            String url = DbTypeTools.getDataSourceUrl(adDatasource.getDatasourceIp(), adDatasource.getDatasourcePort(), adDatasource.getDatasourceDatabase(), dbSourceTypeEnum);
            Properties env = new Properties();
            env.put("url", url);
            env.put("username", adDatasource.getDatasourceUser());
            env.put("password", adDatasource.getDatasourcePassword());
            env.put("driver", DbTypeTools.getDataSourceDriver(dbSourceTypeEnum));

            DbUtilsHelper dbUtilsHelper = new DbUtilsHelper(env);
            QueryRunner runner = dbUtilsHelper.getRunner();
            switch (dbSourceTypeEnum) {
                case MySql:
                case PostgreSql:
                    querySqlString = querySqlString.replaceAll("\\r|\\n", " ");
                    List<Map<String, Object>> mapListResult = runner.query(querySqlString, new MapListHandler());
                    for (Map<String, Object> mapObj : mapListResult) {
                        Map<String, Object> dataRow = new HashMap<>();
                        for (String key : mapObj.keySet()) {
                            for (Object columnObj : dataColumnsMap) {
                                JSONObject columnMap = (JSONObject) columnObj;
                                if (columnMap.get("Column").equals(key)) {
                                    dataRow.put(key, mapObj.get(key));
                                }
                            }
                        }
                        data.add(dataRow);
                        if (data.size() >= Constant.MAX_DATASET_ROW_COUNT) {
                            break;
                        }
                    }
                    break;
            }

        } catch (SQLException e) {
            logger.error("loadFromDatabase", e);
        }
        return data;
    }

    private List<Map<String, Object>> loadFromService(List<Map<String, Object>> data, JSONObject dataSetDefine, List<Map<String, Object>> params, AdUser user, List<AdRole> roles) {
        try {
            //List<Object> dataColumnsMap = dataSetDefine.getJSONArray("DataColumnsMap");
            String queryUrlAddress = dataSetDefine.getJSONObject("DataServiceMeta").getString("QueryUrlAddress");
            String queryUrlFunction = dataSetDefine.getJSONObject("DataServiceMeta").getString("QueryUrlFunction");
            List<Object> queryUrlParams = dataSetDefine.getJSONObject("DataServiceMeta").getJSONArray("QueryUrlParams");

            for (Object querySqlParam : queryUrlParams) {
                JSONObject queryParam = (JSONObject) querySqlParam;
                String paramName = queryParam.getString("ParamName");
                String paramValue = queryParam.getString("DefaultValue");

                for (Map<String, Object> param : params) {
                    if (param.get("ParamName").toString().equals(paramName)) {
                        paramValue = param.get("ParamValue").toString();
                    }
                }

                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", paramName), paramValue);
            }

            if (user != null && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_USER_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_USER_IDENTITY), user.getUserId().toString());
            }

            if (roles != null && roles.size() > 0 && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_ROLE_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_ROLE_IDENTITY), roles.get(0).getRoleId().toString());
            }

            if (roles != null && roles.size() > 0 && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_ROLES_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_ROLES_IDENTITY), roles.stream().map(AdRole::getRoleId).map(Object::toString).collect(Collectors.joining(",")));
            }

            String responseStr = HttpClientUtil.sendHttpGet(queryUrlAddress);

            logger.debug(responseStr);

            if (!StringUtils.isEmpty(queryUrlFunction)) {
                responseStr = JavaScriptUtil.execute(queryUrlFunction, "parse", responseStr);
                logger.debug(responseStr);
            }
            Object parseObj = JSON.parse(responseStr);

            if (parseObj instanceof JSONObject) {
                data.add((JSONObject) parseObj);
            } else if (parseObj instanceof JSONArray) {
                List<Object> resultArray = (JSONArray) parseObj;
                for (Object obj : resultArray) {
                    JSONObject jsObj = (JSONObject) obj;
                    data.add(jsObj);
                }
            }

        } catch (Exception e) {
            logger.error("loadFromService", e);
        }
        return data;
    }


    @Override
    public List<String> loadPageParams(Integer pageId) {
        List<String> pageParams = new ArrayList<>();
        try {
            AdPage adPage = adPageService.selectByPrimaryKey(pageId);
            List<Integer> pageChartIds = new ArrayList<>();
            List<String> pageDataSetCodes = new ArrayList<>();
            if (adPage.getPageTypeId().equals("grid")) {
                JSONObject pageData = (JSONObject) JSON.parse(adPage.getPageData());
                JSONArray content = pageData.getJSONArray("content");
                pageChartIds = findGidPageCharts(pageChartIds, content);
            } else if (adPage.getPageTypeId().equals("free")) {
                JSONArray pageData = (JSONArray) JSON.parse(adPage.getPageData());
                pageChartIds = findFreePageCharts(pageChartIds, pageData);
            }
            for (Integer chartId : pageChartIds) {
                AdChart adChart = adChartService.selectByPrimaryKey(chartId);
                if (adChart != null) {
                    JSONObject chartData = (JSONObject) JSON.parse(adChart.getChartData());
                    pageDataSetCodes = findChartDataSetCode(pageDataSetCodes, chartData);
                }
            }
            for (String dataSetCode : pageDataSetCodes) {
                AdDataset adDataset = adDatasetService.selectAdDatasetByCode(dataSetCode);
                if (adDataset != null) {
                    JSONObject dataSetObj = (JSONObject) JSON.parse(adDataset.getDatasetData());
                    if (dataSetObj != null && dataSetObj.getString("DataServiceType").equals("database")) {
                        JSONArray querySqlParams = dataSetObj.getJSONObject("DataServiceMeta").getJSONArray("QuerySqlParams");
                        for (Object queryParam : querySqlParams) {
                            JSONObject queryParamJsonObj = (JSONObject) queryParam;
                            if (queryParamJsonObj != null) {
                                String paramName = queryParamJsonObj.getString("ParamName");
                                if (paramName != null && !pageParams.contains(paramName)) {
                                    pageParams.add(paramName);
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            logger.error("loadPageParams", e);
        }
        return pageParams;
    }


    private List<String> findChartDataSetCode(List<String> dataSetCodes, JSONObject chartData) {
        if (chartData != null) {
            JSONArray bindings = chartData.getJSONArray("Binding");
            if (bindings != null) {
                for (Object binding : bindings) {
                    JSONObject jsonBinding = (JSONObject) binding;
                    if (jsonBinding != null) {
                        String dataSetCode = jsonBinding.getString("DataSetCode");
                        if (dataSetCode != null && !dataSetCodes.contains(dataSetCode)) {
                            dataSetCodes.add(dataSetCode);
                        }
                    }
                }
            }
        }
        return dataSetCodes;
    }


    private List<Integer> findGidPageCharts(List<Integer> chatIds, JSONArray content) {
        if (content != null)
            for (Object item : content) {
                JSONObject jsonItem = (JSONObject) item;
                if (jsonItem != null) {
                    JSONObject component = jsonItem.getJSONObject("componentState");
                    if (component != null) {
                        Integer chartId = component.getInteger("chartId");
                        if (chartId != null && !chatIds.contains(chartId)) {
                            chatIds.add(chartId);
                        }
                    }
                    JSONArray contentInner = jsonItem.getJSONArray("content");
                    if (contentInner != null) {
                        chatIds = findGidPageCharts(chatIds, contentInner);
                    }
                }
            }
        return chatIds;
    }

    private List<Integer> findFreePageCharts(List<Integer> chatIds, JSONArray items) {
        if (items != null)
            for (Object item : items) {
                JSONObject jsonItem = (JSONObject) item;
                if (jsonItem != null) {
                    JSONObject options = jsonItem.getJSONObject("options");
                    if (options != null) {
                        Integer chartId = options.getInteger("chartId");
                        if (chartId != null && !chatIds.contains(chartId)) {
                            chatIds.add(chartId);
                        }
                    }
                }
            }
        return chatIds;
    }


    @Override
    public Map<String, Object> exportPage(Integer pageId) {
        Map<String, Object> data = new HashMap<>();
        AdPage adPage = adPageService.selectByPrimaryKey(pageId);
        List<Integer> pageChartIds = new ArrayList<>();
        List<String> pageDataSetCodes = new ArrayList<>();
        List<Integer> pageSchemaIds = new ArrayList<>();

        List<AdChart> pageChartList = new ArrayList<>();
        List<AdDataset> pageDatasetList = new ArrayList<>();
        List<AdSchema> pageSchemaList = new ArrayList<>();

        if (adPage.getPageTypeId().equals("grid")) {
            JSONObject pageData = (JSONObject) JSON.parse(adPage.getPageData());
            JSONArray content = pageData.getJSONArray("content");
            pageChartIds = findGidPageCharts(pageChartIds, content);
        } else if (adPage.getPageTypeId().equals("free")) {
            JSONArray pageData = (JSONArray) JSON.parse(adPage.getPageData());
            pageChartIds = findFreePageCharts(pageChartIds, pageData);
        }

        for (Integer chartId : pageChartIds) {
            AdChart adChart = adChartService.selectByPrimaryKey(chartId);
            if (adChart != null) {
                pageChartList.add(adChart);
                JSONObject chartData = (JSONObject) JSON.parse(adChart.getChartData());
                pageDataSetCodes = findChartDataSetCode(pageDataSetCodes, chartData);
            }
        }
        for (String dataSetCode : pageDataSetCodes) {
            AdDataset adDataset = adDatasetService.selectAdDatasetByCode(dataSetCode);
            if (adDataset != null) {
                pageDatasetList.add(adDataset);
                Integer schemaId = adDataset.getSchemaId();
                if (!pageSchemaIds.contains(schemaId)) {
                    pageSchemaIds.add(schemaId);
                    AdSchema adSchema = adSchemaService.selectByPrimaryKey(schemaId);
                    pageSchemaList.add(adSchema);
                }
            }
        }

        data.put("page", adPage);
        data.put("charts", pageChartList);
        data.put("datasets", pageDatasetList);
        data.put("schemas", pageSchemaList);

        return data;
    }

    @Override
    @Transactional
    public void importPage(Integer pageId, Map<String, Object> data) {
        AdPage adPage = (AdPage) data.get("page");
        List<AdChart> pageCharts = (List<AdChart>) data.get("charts");
        List<AdDataset> pageDataSets = (List<AdDataset>) data.get("datasets");
        List<AdSchema> pageSchemas = (List<AdSchema>) data.get("schemas");

        AdPage currentPage = adPageService.selectByPrimaryKey(pageId);

        adPage.setPageId(pageId);
        adPage.setPageFolderId(currentPage.getPageFolderId());

        AdFolder adChartFolder = new AdFolder();
        if (pageCharts.size() > 0) {
            adChartFolder.setFolderName(adPage.getPageName());
            adChartFolder.setFolderParent(-1);
            adChartFolder.setFolderType(2);
            adChartFolder.setFolderId(adFolderService.insert(adChartFolder));
        }


        AdFolder adSchemaFolder = new AdFolder();
        if (pageSchemas.size() > 0) {
            adSchemaFolder.setFolderName(adPage.getPageName());
            adSchemaFolder.setFolderParent(-1);
            adSchemaFolder.setFolderType(1);
            adSchemaFolder.setFolderId(adFolderService.insert(adSchemaFolder));
        }

        if (adPage.getPageTypeId().equals("grid")) {
            JSONObject pageData = (JSONObject) JSON.parse(adPage.getPageData());
            pageData = parseGridPage(pageData, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
            adPage.setPageData(JSON.toJSONString(pageData));
        } else if (adPage.getPageTypeId().equals("free")) {
            JSONArray pageData = (JSONArray) JSON.parse(adPage.getPageData());
            pageData = parseFreePage(pageData, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
            adPage.setPageData(JSON.toJSONString(pageData));
        }

        adPageService.updateByPrimaryKeyWithBLOBs(adPage);
    }

    private JSONObject parseGridPage(JSONObject pageData, AdPage adPage, List<AdChart> pageCharts, List<AdDataset> pageDataSets, List<AdSchema> pageSchemas, AdFolder adChartFolder, AdFolder adSchemaFolder) {
        JSONArray content = pageData.getJSONArray("content");
        if (content != null) {
            for (Object item : content) {
                JSONObject jsonItem = (JSONObject) item;
                if (jsonItem != null) {
                    JSONObject component = jsonItem.getJSONObject("componentState");
                    if (component != null) {
                        if (pageCharts.size() > 0) {
                            Integer chartId = component.getInteger("chartId");
                            Integer newChartId = updatePageChart(chartId, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
                            if (newChartId != -1) {
                                component.remove("chartId");
                                component.put("chartId", newChartId);
                            }
                        }
                    }
                    parseGridPage(jsonItem, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
                }
            }
        }
        return pageData;
    }

    private JSONArray parseFreePage(JSONArray pageData, AdPage adPage, List<AdChart> pageCharts, List<AdDataset> pageDataSets, List<AdSchema> pageSchemas, AdFolder adChartFolder, AdFolder adSchemaFolder) {
        if (pageData != null) {
            for (Object item : pageData) {
                JSONObject jsonItem = (JSONObject) item;
                if (jsonItem != null) {
                    JSONObject options = jsonItem.getJSONObject("options");
                    if (options != null) {
                        if (pageCharts.size() > 0) {
                            Integer chartId = options.getInteger("chartId");
                            Integer newChartId = updatePageChart(chartId, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
                            if (newChartId != -1) {
                                options.remove("chartId");
                                options.put("chartId", newChartId);
                            }

                        }
                    }
                }
            }
        }
        return pageData;
    }

    private Integer updatePageChart(Integer oldChartId, AdPage adPage, List<AdChart> pageCharts, List<AdDataset> pageDataSets, List<AdSchema> pageSchemas, AdFolder adChartFolder, AdFolder adSchemaFolder) {
        Integer newChartId = -1;
        for (AdChart adChart : pageCharts) {
            if (adChart.getChartId().equals(oldChartId)) {
                JSONObject chartData = (JSONObject) JSON.parse(adChart.getChartData());
                if (chartData != null) {
                    JSONArray bindings = chartData.getJSONArray("Binding");
                    if (bindings != null) {
                        for (Object binding : bindings) {
                            JSONObject jsonBinding = (JSONObject) binding;
                            if (jsonBinding != null) {
                                if (pageDataSets.size() > 0) {
                                    String dataSetCode = jsonBinding.getString("DataSetCode");
                                    updateChartDataset(dataSetCode, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
                                }
                            }
                        }
                    }
                }

                AdChart cloneChart = (AdChart) ObjectUtils.clone(adChart);
                cloneChart.setChartId(null);
                cloneChart.setChartFolderId(adChartFolder.getFolderId());
                cloneChart.setChartData(JSON.toJSONString(chartData));
                newChartId = adChartService.insert(cloneChart);
            }
        }
        return newChartId;
    }

    private void updateChartDataset(String dataSetCode, AdPage adPage, List<AdChart> pageCharts, List<AdDataset> pageDataSets, List<AdSchema> pageSchemas, AdFolder adChartFolder, AdFolder adSchemaFolder) {
        for (AdDataset adDataset : pageDataSets) {
            if (adDataset.getDatasetCode().equals(dataSetCode)) {
                AdDataset existDataSet = adDatasetService.selectAdDatasetByCode(dataSetCode);
                if (existDataSet == null) {
                    if (pageSchemas.size() > 0) {
                        Integer oldSchemaId = adDataset.getSchemaId();
                        Integer newSchemaId = updateDatasetSchema(oldSchemaId, adPage, pageCharts, pageDataSets, pageSchemas, adChartFolder, adSchemaFolder);
                        if (newSchemaId != -1) {
                            AdDataset cloneDataset = (AdDataset) ObjectUtils.clone(adDataset);
                            cloneDataset.setDatasetId(null);
                            cloneDataset.setSchemaId(newSchemaId);
                            adDatasetService.insert(cloneDataset);
                        }
                    }
                }
            }
        }
    }


    private Integer updateDatasetSchema(Integer oldSchemaId, AdPage adPage, List<AdChart> pageCharts, List<AdDataset> pageDataSets, List<AdSchema> pageSchemas, AdFolder adChartFolder, AdFolder adSchemaFolder) {
        Integer newSchemaId = -1;
        for (AdSchema adSchema : pageSchemas) {
            if (adSchema.getSchemaId().equals(oldSchemaId)) {
                String schemaCode = adSchema.getSchemaCode();
                AdSchema existSchema = adSchemaService.selectAdSchemaByCode(schemaCode);
                AdSchema cloneSchema = (AdSchema) ObjectUtils.clone(adSchema);
                if (existSchema != null) {
                    cloneSchema.setSchemaCode(UUIDUtils.generateShortUuid());
                }
                cloneSchema.setSchemaId(null);
                cloneSchema.setSchemaFolderId(adSchemaFolder.getFolderId());
                newSchemaId = adSchemaService.insert(cloneSchema);
            }
        }
        return newSchemaId;
    }
}
